﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Jungle.Code.DAL;
using Jungle.Code.Utils;
using System.Data.SqlClient;
using System.Data;

namespace Jungle.Code.CategoryManager
{
    public class JungleTreeMenu
    {
        public string err;
        private static string ConnectionString = DBHelper.ConnectionString;

        public List<JungleTreeMenu> MenuItemsList;
        
        #region Fields
        private int id;
        private int left_key;
        private int right_key;
        private int levelid;
        private string menutext;
        private int parentLevelId;
        private int parentRightKey;
        private int parentLeftKey;
        private bool menuVisible;
        #endregion

        #region Properties
        public int ID { get { return id; } set { id = value; } }
        public int LeftKey { get { return left_key; } set { left_key = value; } }
        public int RightKey { get { return right_key; } set { right_key = value; } }
        public int LevelId { get { return levelid; } set { levelid = value; } }
        public string MenuText { get { return menutext; } set { menutext = value; } }
        public int ParentLevelId { get { return parentLevelId; } set { parentLevelId = value; } }
        public int ParentRightKey { get { return parentRightKey; } set { parentRightKey = value; } }
        public int ParentLeftKey { get { return parentLeftKey; } set { parentLeftKey = value; } }
        public bool MenuVisible { get { return menuVisible; } set { menuVisible = value; } }
        #endregion

        //---------------------------------------------------------------------------------
        public void AddMenuItem()
        {
            SqlConnection connect = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand("menu.MenuItem_Add", connect);
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                command.Parameters.Add("@levelId", SqlDbType.Int).Value = ParentLevelId;
                command.Parameters.Add("@right_key", SqlDbType.Int).Value = ParentRightKey;
                command.Parameters.Add("@menuText",SqlDbType.VarChar).Value = MenuText;
                command.Parameters.Add("@menuVisible", SqlDbType.Bit).Value = MenuVisible;
                
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                err = ex.ToString();
            }
            finally
            {
                command.Connection.Close();
            }
        }
        //---------------------------------------------------------------------------------
        public int GetAll()
        {
            int count = 0;
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("menu.MenuItem_GetAll", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                
                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    JungleTreeMenu tm = new JungleTreeMenu();

                    tm.ID = (int)reader["ID"];
                    tm.LeftKey = (int)reader["left_key"];
                    tm.MenuText = reader["menuText"].ToString().Trim();
                    tm.RightKey = (int)reader["right_key"];
                    tm.LevelId = (int)reader["levelId"];
                    tm.MenuVisible = (bool)reader["menuVisible"];

                    MenuItemsList.Add(tm);
                    
                    count++;
                }

                reader.Close();
                command.Connection.Close();

            }
            return count;
        }
        //---------------------------------------------------------------------------------
        public void GetById()
        {
            
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("menu.MenuItem_GetById", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    ID = (int)reader["ID"];
                    LeftKey = (int)reader["left_key"];
                    MenuText = reader["menuText"].ToString().Trim();
                    RightKey = (int)reader["right_key"];
                    LevelId = (int)reader["levelId"];
                    MenuVisible = (bool)reader["menuVisible"];
                }

                reader.Close();
                command.Connection.Close();

            }
            
        }
        //---------------------------------------------------------------------------------
        public int GetMainItems()
        {
            int count = 0;
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("menu.MenuItem_GetMainItems", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    JungleTreeMenu tm = new JungleTreeMenu();

                    tm.ID = (int)reader["ID"];
                    tm.LeftKey = (int)reader["left_key"];
                    tm.MenuText = reader["menuText"].ToString().Trim();
                    tm.RightKey = (int)reader["right_key"];
                    tm.LevelId = (int)reader["levelId"];
                    tm.MenuVisible = (bool)reader["menuVisible"];

                    MenuItemsList.Add(tm);

                    count++;
                }

                reader.Close();
                command.Connection.Close();

            }
            return count;
        }
        //---------------------------------------------------------------------------------
        public void Delete()
        {
            SqlConnection connect = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand("menu.MenuItem_Delete", connect);
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                command.Parameters.Add("@left_key", SqlDbType.Int).Value = LeftKey;
                command.Parameters.Add("@right_key", SqlDbType.Int).Value = RightKey;

                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                err = ex.ToString();
            }
            finally
            {
                command.Connection.Close();
            }
        }
        //---------------------------------------------------------------------------------
        public void DeletAllMenuItems()
        {
            SqlConnection connect = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand("menu.MenuItem_DeleteAllMenuItems", connect);
            command.CommandType = CommandType.StoredProcedure;
            try
            {            
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                err = ex.ToString();
            }
            finally
            {
                command.Connection.Close();
            }
        }
        //---------------------------------------------------------------------------------
        public int GetParentIdList()
        {
            int count = 0;
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("menu.MenuItem_GetParentItems", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@left_key", SqlDbType.Int).Value = LeftKey;
                command.Parameters.Add("@right_key", SqlDbType.Int).Value = RightKey;
                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    JungleTreeMenu tm = new JungleTreeMenu();

                    tm.ID = (int)reader["ID"];
                    tm.LeftKey = (int)reader["left_key"];
                    tm.MenuText = reader["menuText"].ToString().Trim();
                    tm.RightKey = (int)reader["right_key"];
                    tm.LevelId = (int)reader["levelId"];
                    tm.MenuVisible = (bool)reader["menuVisible"];
                    MenuItemsList.Add(tm);

                    count++;
                }

                reader.Close();
                command.Connection.Close();

            }
            return count;

        }
        //---------------------------------------------------------------------------------
        public int GetChildItems()
        {
            int count = 0;
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("menu.MenuItem_GetChildItems", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@left_key", SqlDbType.Int).Value = LeftKey;
                command.Parameters.Add("@right_key", SqlDbType.Int).Value = RightKey;
                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    JungleTreeMenu tm = new JungleTreeMenu();

                    tm.ID = (int)reader["ID"];
                    tm.LeftKey = (int)reader["left_key"];
                    tm.MenuText = reader["menuText"].ToString().Trim();
                    tm.RightKey = (int)reader["right_key"];
                    tm.LevelId = (int)reader["levelId"];
                    tm.MenuVisible = (bool)reader["menuVisible"];
                    MenuItemsList.Add(tm);

                    count++;
                }

                reader.Close();
                command.Connection.Close();

            }
            return count;
        }
        //---------------------------------------------------------------------------------
        public int GetChildsFirstUnderLevel()
        {
            int count = 0;
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("menu.MenuItem_GetFirsUnderLevelChilds", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@left_key", SqlDbType.Int).Value = LeftKey;
                command.Parameters.Add("@right_key", SqlDbType.Int).Value = RightKey;
                command.Parameters.Add("@level", SqlDbType.Int).Value = LevelId;
                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    JungleTreeMenu tm = new JungleTreeMenu();

                    tm.ID = (int)reader["ID"];
                    tm.LeftKey = (int)reader["left_key"];
                    tm.MenuText = reader["menuText"].ToString().Trim();
                    tm.RightKey = (int)reader["right_key"];
                    tm.LevelId = (int)reader["levelId"];
                    tm.MenuVisible = (bool)reader["menuVisible"];

                    MenuItemsList.Add(tm);

                    count++;
                }

                reader.Close();
                command.Connection.Close();

            }
            return count;
        }

        public void Update()
        {
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("menu.MenuItem_Update", connect);
                
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
                command.Parameters.Add("@right_key", SqlDbType.Int).Value = RightKey;
                command.Parameters.Add("@left_key", SqlDbType.Int).Value = LeftKey;
                command.Parameters.Add("@levelId", SqlDbType.Int).Value = LevelId;
                command.Parameters.Add("@menuText", SqlDbType.VarChar).Value = MenuText;
                command.Parameters.Add("@menuVisible", SqlDbType.Bit).Value = MenuVisible;
                command.Connection.Open();

                command.ExecuteNonQuery();

                command.Connection.Close();

            }
        }
        //---------------------------------------------------------------------------------
        public static int GetMaxRightKey()
        {
            
            int right_max = 0;            

            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                
                SqlCommand command = new SqlCommand("[menu].[MenuItem_GetMaxRight]", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                reader = command.ExecuteReader();

                if (reader == null) { return 0; }

                while (reader.Read())
                {                    
                    right_max = (int)reader[0];
                }
                reader.Close();
                command.Connection.Close();

               

            }
            return right_max;
        }
        //---------------------------------------------------------------------------------
        public void GetParentMenuId(int left_keyV, int right_keyV, int levelIdV)
        {
            
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("menu.MenuItem_GetParentMenu", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@left_key", SqlDbType.Int).Value = left_keyV;
                command.Parameters.Add("@right_key", SqlDbType.Int).Value = right_keyV;
                command.Parameters.Add("@level", SqlDbType.Int).Value = levelIdV;
                command.Connection.Open();
                reader = command.ExecuteReader();
            
                while (reader.Read())
                {
                    RightKey = (int)reader["right_key"];
                    LeftKey = (int)reader["left_key"];
                    ID = (int)reader["ID"];
                    MenuText = reader["menuText"].ToString().Trim();
                }

                reader.Close();
                command.Connection.Close();
                
            }
            
        }

        //----------------------------------------TEST-------------------------------------
        private string Tests()
        {
            string resultString = "";
            //Шаг 1: Левый ключ ВСЕГДА меньше правого
            if (Step1() > 0) { resultString += "1"; }
            //Шаг 2: Наименьший левый ключ ВСЕГДА равен 1;
            //Шаг 3: Наибольший правый ключ ВСЕГДА равен двойному числу узлов;
            if (Step2_3() != true) { resultString += "23"; }
            //Шаг 4: Разница между правым и левым ключом ВСЕГДА нечетное число;


            return resultString;
        }
        //---------------------------------------------------------------------------------
        private int Step1()
        {
            int count = 0;
            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("SELECT ID FROM [menu].[MenuTree] WHERE [left_key] >= [right_key]", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.Text;

                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {                
                    count++;
                }

                reader.Close();
                command.Connection.Close();

            }
            return count;
        }
        //---------------------------------------------------------------------------------
        private bool Step2_3()
        {
            int count = 0;
            int left_min = 0;
            int right_max = 0;
            double temp = 0;

            using (SqlConnection connect = new SqlConnection(ConnectionString))
            {
                MenuItemsList = new List<JungleTreeMenu>();
                SqlCommand command = new SqlCommand("SELECT COUNT(ID), MIN(left_key), MAX(right_key) FROM [menu].[MenuTree]", connect);
                SqlDataReader reader = null;

                command.CommandType = CommandType.Text;

                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    count = (int)reader[0];
                    left_min = (int)reader[1];
                    right_max = (int)reader[2];
                }
                reader.Close();
                command.Connection.Close();

                if (count > 0) { temp = right_max / count; }

                if (temp == 2 && left_min == 1) { return true; }

            }
            return false;
        }
        //----------------------------------------TEST-------------------------------------

        public string ChangeOrder(int id_mu, int id_ml)
        {
            JungleTreeMenu jUpperMenu = new JungleTreeMenu(); // меню которое находится выше
            JungleTreeMenu jLowerMenu = new JungleTreeMenu();
            JungleTreeMenu parentOfMenus = new JungleTreeMenu();

            jUpperMenu.ID = id_mu;
            jUpperMenu.GetById();
           

            jLowerMenu.ID = id_ml;
            jLowerMenu.GetById();
            

            int SkewL = jUpperMenu.RightKey - jUpperMenu.LeftKey+1;
            int SkewR = jLowerMenu.RightKey - jLowerMenu.LeftKey+1;                      
                    
            ChangeKeys(jUpperMenu.LeftKey, jUpperMenu.RightKey, jLowerMenu.LeftKey, jLowerMenu.RightKey, SkewL, SkewR);
            
            return "OK_SL_" + SkewL.ToString() + "__SR_" + SkewR.ToString() + "__MU_" + jUpperMenu.MenuText + "__ML_" + jLowerMenu.MenuText;

        }

        public void ChangeKeys(int L_left_key, int L_right_key, int R_left_key, int R_right_key, int LSkew, int RSkew)
        {
            SqlConnection connect = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand("menu.MenuItem_MoveDown", connect);
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                command.Parameters.Add("@left_keyL", SqlDbType.Int).Value = L_left_key;
                command.Parameters.Add("@right_keyL", SqlDbType.Int).Value = L_right_key;
                command.Parameters.Add("@left_keyR", SqlDbType.Int).Value = R_left_key;
                command.Parameters.Add("@right_keyR", SqlDbType.Int).Value = R_right_key;
                command.Parameters.Add("@skewL", SqlDbType.Int).Value = LSkew;
                command.Parameters.Add("@skewR", SqlDbType.Int).Value = RSkew;

                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                err = ex.ToString();
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public string PrepareForMove(int ID, int operation)
        {
            // operation 0 - up 1 - down
            string rez = "";
            JungleTreeMenu jMenu = new JungleTreeMenu();
            JungleTreeMenu jParent = new JungleTreeMenu();

            jMenu.ID = ID;
            jMenu.GetById();

            jParent.GetParentMenuId(jMenu.LeftKey, jMenu.RightKey, jMenu.LevelId);
            jParent.GetById();
            int childItemsCount = jParent.GetChildsFirstUnderLevel();
            int jMenuPosition=5;

            for (int i = 0; i <= childItemsCount-1; i++) { if (jParent.MenuItemsList.ElementAt(i).ID == jMenu.ID) { jMenuPosition = i; } }

            if (operation == 0)
            {
                if (jMenuPosition < 1) { return "Поднять нельзя" + jMenuPosition.ToString(); } 
                else 
                {                    
                    rez = "Поднять можно" + ChangeOrder(jParent.MenuItemsList.ElementAt(jMenuPosition - 1).ID, jMenu.ID); 
                }

            }
            else
            {
                if (jMenuPosition == childItemsCount-1) { return "Опустить нельзя" + jMenuPosition.ToString(); } 
                else 
                {
                    rez = "Опустить можно" + ChangeOrder(jMenu.ID, jParent.MenuItemsList.ElementAt(jMenuPosition + 1).ID);
                }
            }
            return rez;
        }
    }
}
